﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class ReceiveDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public ReceiveDAL()
        {

        }
        public IList<M_IcmReceiveObject> GetReceiveObject(Nullable<int> Sequence)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_IcmReceiveObject> infoList = new List<M_IcmReceiveObject>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@icm_receive_seq", Sequence);

                strSQL = " SELECT rc.*"
                            + ", wh.ICM_WH_NAME, dn.ICM_DN_REF_NO, dn.ICM_DN_NAME, t.RECEIVE_TYPE_NAME"
                            + ", rt.ICM_RES_TYPE_NAME, j.JOB_SITE_NAME"
                            + ", CONCAT(dn.ICM_DN_REF_NO, '(', dn.ICM_DN_NAME, ')') AS ICM_DN_CODE"
                        + " FROM ICM_RECEIVE rc"
                        + " LEFT JOIN ICM_MAS_WAREHOUSE wh ON rc.ICM_WH_SEQ = wh.ICM_WH_SEQ"
                        + " LEFT JOIN ICM_MAS_RECEIVE_TYPE t ON rc.RECEIVE_TYPE_SEQ = t.RECEIVE_TYPE_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_TYPE rt ON rc.ICM_RES_TYPE_SEQ = rt.ICM_RES_TYPE_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES j ON rc.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                        + " LEFT JOIN ICM_DELIVERY_NOTE dn ON rc.ICM_DN_SEQ = dn.ICM_DN_SEQ"
                        + " WHERE rc.RECORD_STATUS <> @DeleteRecord "
                        + (Sequence.HasValue ? " AND rc.ICM_RECEIVE_SEQ = @icm_receive_seq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_IcmReceiveObject info = new M_IcmReceiveObject();

                        info.icm_receive_seq = Convert.ToInt32(drc[i]["ICM_RECEIVE_SEQ"]);
                        info.icm_receive_code = drc[i]["ICM_RECEIVE_CODE"].ToString();
                        info.icm_receive_name = drc[i]["icm_receive_name"].ToString();
                        info.receive_type_seq = Convert.ToInt32(drc[i]["RECEIVE_TYPE_SEQ"].ToString());
                        info.receive_type_name = drc[i]["RECEIVE_TYPE_NAME"].ToString();
                        info.icm_dn_seq = cn.ConvertNullInt(drc[i]["ICM_DN_SEQ"]);
                        info.icm_dn_code = drc[i]["ICM_DN_CODE"].ToString();
                        info.icm_wh_seq = cn.ConvertNullInt(drc[i]["ICM_WH_SEQ"]);
                        info.icm_wh_name = drc[i]["ICM_WH_NAME"].ToString();
                        info.job_site_seq = cn.ConvertNullInt(drc[i]["JOB_SITE_SEQ"]);
                        info.job_site_name = drc[i]["JOB_SITE_NAME"].ToString();
                        info.icm_res_type_seq = cn.ConvertNullInt(drc[i]["ICM_RES_TYPE_SEQ"].ToString());
                        info.icm_res_type_name = drc[i]["ICM_RES_TYPE_NAME"].ToString();
                        info.tranfer_name = drc[i]["TRANF_NAME"].ToString();
                        info.tranfer_ref_no = drc[i]["TRANF_REF_NO"].ToString();
                        info.receive_date = cn.ConvertNullDateTime(drc[i]["RECEIVE_DATE"]);
                        info.remark = drc[i]["REMARK"].ToString();
                        info.created_when = Convert.ToDateTime(string.Format("{0:dd/MM/yyyy}", drc[i]["CREATED_WHEN"]));

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        public DataSet GenReceiveCode()
        {
            DataSet ds = new DataSet();
            dbManager.Open();

            string sql = " SELECT case when  (Year(Now()) <2500) then Year(Now())+543 else Year(Now()) end YearValue , Month(Now()) as MonthValue "
                         + " ,COUNT(icm_receive_code)+1 as running  "
                         + " FROM icm_receive "
                         + " WHERE YEAR(created_when) = YEAR(Now()) and  Month(created_when) = Month(Now())";

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }

        public Int32 GetReceiveSeq(string asset_code_start, string asset_code_finish)
        {
            string strSQL;
            DataRowCollection drc = null;
            Int32 picm_receive_seq = 0;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@asset_code_start", asset_code_start);
                dbManager.AddParameters(1, "@asset_code_finish", asset_code_finish);

                strSQL = " SELECT icm_receive_seq,asset_code_start,asset_code_finish "
                        + " FROM icm_receive "
                        + " WHERE asset_code_start =@asset_code_start and asset_code_finish=@asset_code_finish ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    picm_receive_seq = Convert.ToInt32(drc[0]["icm_receive_seq"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return picm_receive_seq;
        }



        public void Insert(M_IcmReceiveObject info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@icm_receive_code", info.icm_receive_code);
                dbManager.AddParameters(1, "@icm_receive_name", info.icm_receive_name);
                dbManager.AddParameters(2, "@icm_res_type_seq", info.icm_res_type_seq);
                dbManager.AddParameters(3, "@icm_dn_seq", info.icm_dn_seq);
                dbManager.AddParameters(4, "@icm_wh_seq", info.icm_wh_seq);
                dbManager.AddParameters(5, "@job_site_seq", info.job_site_seq);
                dbManager.AddParameters(6, "@tranf_name", info.tranfer_name);
                dbManager.AddParameters(7, "@tranf_ref_no", info.tranfer_ref_no);
                dbManager.AddParameters(8, "@receive_date", info.receive_date);
                dbManager.AddParameters(9, "@receive_type_seq", info.receive_type_seq);
                dbManager.AddParameters(10, "@remark", info.remark);
                dbManager.AddParameters(11, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(12, "@created_by", info.created_by);
                dbManager.AddParameters(13, "@created_when", DateTime.Now);

                strSQL = " INSERT INTO ICM_RECEIVE "
                        + " (ICM_RECEIVE_CODE, ICM_RECEIVE_NAME, ICM_DN_SEQ, RECEIVE_TYPE_SEQ, ICM_RES_TYPE_SEQ, RECEIVE_DATE"
                        + ", TRANF_NAME, TRANF_REF_NO, ICM_WH_SEQ, JOB_SITE_SEQ"
                        + ", REMARK, RECORD_STATUS,CREATED_BY,CREATED_WHEN) "
                        + "  VALUES "
                        + " (@icm_receive_code, @icm_receive_name, @icm_dn_seq, @receive_type_seq,@icm_res_type_seq, @receive_date"
                        + ", @tranf_name, @tranf_ref_no,@icm_wh_seq, @job_site_seq, @remark, @record_status, @created_by, @created_when) ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Update(M_IcmReceiveObject info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@icm_receive_seq", info.icm_receive_seq);
                dbManager.AddParameters(1, "@icm_receive_code", info.icm_receive_code);
                dbManager.AddParameters(2, "@icm_receive_name", info.icm_receive_name);
                dbManager.AddParameters(3, "@icm_res_type_seq", info.icm_res_type_seq);
                dbManager.AddParameters(4, "@icm_dn_seq", info.icm_dn_seq);
                dbManager.AddParameters(5, "@icm_wh_seq", info.icm_wh_seq);
                dbManager.AddParameters(6, "@job_site_seq", info.job_site_seq);
                dbManager.AddParameters(7, "@tranf_name", info.tranfer_name);
                dbManager.AddParameters(8, "@tranf_ref_no", info.tranfer_ref_no);
                dbManager.AddParameters(9, "@receive_date", info.receive_date);
                dbManager.AddParameters(10, "@receive_type_seq", info.receive_type_seq);
                dbManager.AddParameters(11, "@remark", info.remark);
                dbManager.AddParameters(12, "@record_status", info.record_status);
                dbManager.AddParameters(13, "@updated_by", info.updated_by);
                dbManager.AddParameters(14, "@updated_when", info.updated_when);

                strSQL = " UPDATE ICM_RECEIVE "
                        + " SET ICM_RECEIVE_CODE = @icm_receive_code"
                        + ", ICM_RECEIVE_NAME = @icm_receive_name"
                        + ", ICM_DN_SEQ = @icm_dn_seq"
                        + ", RECEIVE_TYPE_SEQ = @receive_type_seq"
                        + ", ICM_RES_TYPE_SEQ = @icm_res_type_seq"
                        + ", RECEIVE_DATE = @receive_date"
                        + ", TRANF_NAME = @tranf_name"
                        + ", TRANF_REF_NO = @tranf_ref_no"
                        + ", ICM_WH_SEQ = @icm_wh_seq"
                        + ", JOB_SITE_SEQ = @job_site_seq"
                        + ", REMARK = @remark"
                        //+ ", RECORD_STATUS = @record_status"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                        + " WHERE ICM_RECEIVE_SEQ = @icm_receive_seq ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Update_Icm_Receive_Asset_Interval(M_IcmReceiveObject info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);
                dbManager.AddParameters(0, "@icm_receive_seq", info.icm_receive_seq);
                dbManager.AddParameters(1, "@trans_mas_seq", info.trans_mas_seq);
                dbManager.AddParameters(2, "@icm_res_type_seq", info.icm_res_type_seq);
                dbManager.AddParameters(3, "@quantity", info.quantity);
                dbManager.AddParameters(4, "@price", info.price);
                dbManager.AddParameters(5, "@quantity_remain_before", info.quantity_remain_before);
                dbManager.AddParameters(6, "@quantity_remain_after", info.quantity_remain_after);
                dbManager.AddParameters(7, "@quantity_remain", info.quantity_remain);
                dbManager.AddParameters(8, "@receive_date", info.receive_date);
                dbManager.AddParameters(9, "@asset_code_start", info.asset_code_start);
                dbManager.AddParameters(10, "@asset_code_finish", info.asset_code_finish);
                dbManager.AddParameters(11, "@remark", info.remark);
                dbManager.AddParameters(12, "@record_status", info.record_status);
                dbManager.AddParameters(13, "@updated_by", info.updated_by);
                dbManager.AddParameters(14, "@updated_when", DateTime.Now);

                strSQL = " UPDATE  icm_receive "
                         + " SET trans_mas_seq = @trans_mas_seq "
                         + " ,icm_res_type_seq = @icm_res_type_seq "
                         + " ,quantity = @quantity "
                         + " ,price = @price "
                         + " ,quantity_remain_before = @quantity_remain_before "
                         + " ,quantity_remain_after = @quantity_remain_after "
                         + " ,quantity_remain = @quantity_remain "
                         + " ,receive_date = @receive_date "
                         + " ,asset_code_start = @asset_code_start "
                         + " ,asset_code_finish = @asset_code_finish "
                         + " ,remark = @remark "
                         + " ,record_status = @record_status "
                         + " ,updated_by = @updated_by "
                         + " ,updated_when = @updated_when "
                         + " WHERE icm_receive_seq = @icm_receive_seq ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //public void Update(M_IcmReceiveObject info)
        //{
        //    string strSQL;
        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(15);
        //        dbManager.AddParameters(0, "@icm_receive_seq", info.icm_receive_seq);
        //        dbManager.AddParameters(1, "@trans_mas_seq", info.trans_mas_seq);
        //        dbManager.AddParameters(2, "@icm_res_type_seq", info.icm_res_type_seq);
        //        dbManager.AddParameters(3, "@quantity", info.quantity);
        //        dbManager.AddParameters(4, "@price", info.price);
        //        dbManager.AddParameters(5, "@quantity_remain_before", info.quantity_remain_before);
        //        dbManager.AddParameters(6, "@quantity_remain_after", info.quantity_remain_after);
        //        dbManager.AddParameters(7, "@quantity_remain", info.quantity_remain);
        //        dbManager.AddParameters(8, "@receive_date", info.receive_date);
        //        dbManager.AddParameters(9, "@asset_code_start", info.asset_code_start);
        //        dbManager.AddParameters(10, "@asset_code_finish", info.asset_code_finish);
        //        dbManager.AddParameters(11, "@remark", info.remark);
        //        dbManager.AddParameters(12, "@record_status", info.record_status);
        //        dbManager.AddParameters(13, "@updated_by", info.updated_by);
        //        dbManager.AddParameters(14, "@updated_when", info.updated_when);

        //        strSQL = " UPDATE  icm_receive "
        //                 + " SET trans_mas_seq = @trans_mas_seq "
        //                 + " ,lot_number = @lot_number "
        //                 + " ,icm_res_type_seq = @icm_res_type_seq "
        //                 + " ,quantity = @quantity "
        //                 + " ,price = @price "
        //                 + " ,quantity_remain_before = @quantity_remain_before "
        //                 + " ,quantity_remain_after = @quantity_remain_after "
        //                 + " ,quantity_remain = @quantity_remain "
        //                 + " ,receive_date = @receive_date "
        //                 + " ,asset_code_start = @asset_code_start "
        //                 + " ,asset_code_finish = @asset_code_finish "
        //                 + " ,remark = @remark "
        //                 + " ,record_status = @record_status "
        //                 + " ,updated_by = @updated_by "
        //                 + " ,updated_when = @updated_when "
        //                 + " WHERE icm_receive_seq = @icm_receive_seq ";

        //        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}
        public void Update_Cut_Stock_FIFO_Preview(M_IcmReceiveObject info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@icm_receive_seq", info.icm_receive_seq);
                dbManager.AddParameters(1, "@quantity_remain_after", info.quantity_remain_after);
                dbManager.AddParameters(2, "@cut_stock_by", info.cut_stock_by);
                dbManager.AddParameters(3, "@cut_stock_when", info.cut_stock_when);

                strSQL = " UPDATE  icm_receive "
                         + " SET quantity_remain_after = @quantity_remain_after "
                         + " ,cut_stock_by = @cut_stock_by "
                         + " ,cut_stock_when = @cut_stock_when "
                         + " WHERE icm_receive_seq = @icm_receive_seq ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Update_Cut_Stock_FIFO(M_IcmReceiveObject info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@icm_receive_seq", info.icm_receive_seq);
                dbManager.AddParameters(1, "@quantity_remain", info.quantity_remain);
                dbManager.AddParameters(2, "@cut_stock_by", info.cut_stock_by);
                dbManager.AddParameters(3, "@cut_stock_when", info.cut_stock_when);
                dbManager.AddParameters(4, "@quantity_remain_before", info.quantity_remain_before);

                strSQL = " UPDATE  icm_receive "
                         + " SET quantity_remain = @quantity_remain "
                         + " ,cut_stock_by = @cut_stock_by "
                         + " ,cut_stock_when = @cut_stock_when "
                         + " ,quantity_remain_before = @quantity_remain_before "
                         + " WHERE icm_receive_seq = @icm_receive_seq ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Now);

                strSQL = " UPDATE ICM_RECEIVE "
                         + " SET RECORD_STATUS = @DeleteRecord "
                         + " ,UPDATED_BY = @UpdatedBy "
                         + " ,UPDATED_WHEN = @UpdatedWhen "
                         + " WHERE ICM_RECEIVE_SEQ = @Sequence ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
